package com.autumn.mybatis.provider.sqlserver.builder;

import com.autumn.mybatis.metadata.EntityTable;
import com.autumn.mybatis.provider.sqlserver.AbstractSqlServerProvider;
import com.autumn.mybatis.provider.sqlserver.SqlServerUtils;
import com.autumn.mybatis.provider.util.MybatisSqlUtils;

/**
 * SqlServer 兼容2005 查询生成器
 *
 * @author shao
 * @date 2017/11/21 15:55
 */
public class SqlServerQueryBuilder extends AbstractSqlserverQueryBuilder {

    /**
     * @param dbProvider
     */
    public SqlServerQueryBuilder(AbstractSqlServerProvider dbProvider) {
        super(dbProvider);
    }

    /**
     * 查询一个集合
     *
     * @param table     表
     * @param parmaName 参数名称
     * @return
     */
    @Override
    public String getSelectListSql(EntityTable table, String parmaName) {
        StringBuilder sql = new StringBuilder();
        sql.append("<choose >");
        //分页查询
        sql.append("<when test=\"section.pageClause.offset>0 and section.pageClause.limit>0\">");
        sql.append(selectPageSql(table, MybatisSqlUtils.getAllColumns(table), parmaName));
        sql.append("WHERE ");
        sql.append("P.rownum &gt; ((#{section.pageClause.offset}-1)* #{section.pageClause.limit}) and ");
        //此处应该是小于等于，所以+1之后就是小于
        sql.append("P.rownum &lt; (#{section.pageClause.offset} * #{section.pageClause.limit})+1");
        sql.append("</when>");
        //跳过多少行
        sql.append("<when test=\"section.pageClause.offset>0\">");
        sql.append(selectPageSql(table, MybatisSqlUtils.getAllColumns(table), parmaName));
        sql.append("WHERE P.rownum &gt; #{section.pageClause.offset} ");
        sql.append("</when>");
        //限制查询行数
        sql.append("<when test=\"section.pageClause.limit>0\">");
        sql.append(selectTopSql(table, MybatisSqlUtils.getAllColumns(table), parmaName));
        sql.append("</when>");
        sql.append("<otherwise>");
        //只做条件查询
        sql.append(selectSql(table, MybatisSqlUtils.getAllColumns(table), parmaName));
        sql.append("</otherwise>");
        sql.append("</choose>");
        return sql.toString();
    }


    /**
     * 查询返回指定列
     *
     * @param table     表
     * @param parmaName 参数
     * @return
     */
    @Override
    public String getSelectBySpecifySql(EntityTable table, String parmaName) {
        StringBuilder sql = new StringBuilder();
        sql.append("<choose >");
        //分页查询
        sql.append("<when test=\"section.pageClause.offset>0 and section.pageClause.limit>0\">");
        sql.append(selectPageSql(table, getSelectBySpecifyBaseSql(parmaName), parmaName));
        sql.append("WHERE ");
        sql.append("P.rownum &gt; ((#{section.pageClause.offset}-1)* #{section.pageClause.limit}) and ");
        //此处应该是小于等于，所以+1之后就是小于
        sql.append("P.rownum &lt; (#{section.pageClause.offset} * #{section.pageClause.limit})+1");
        sql.append("</when>");
        //跳过多少行
        sql.append("<when test=\"section.pageClause.offset>0\">");
        sql.append(selectPageSql(table, getSelectBySpecifyBaseSql(parmaName), parmaName));
        sql.append("WHERE P.rownum &gt; #{section.pageClause.offset} ");
        sql.append("</when>");
        //限制查询行数
        sql.append("<when test=\"section.pageClause.limit>0\">");
        sql.append(selectTopSql(table, getSelectBySpecifyBaseSql(parmaName), parmaName));
        sql.append("</when>");
        sql.append("<otherwise>");
        //只做条件查询
        sql.append(selectSql(table, getSelectBySpecifyBaseSql(parmaName), parmaName));
        sql.append("</otherwise>");
        sql.append("</choose>");
        return sql.toString();
    }

    /**
     * 分页查询，返回第一个数据
     *
     * @param table     表
     * @param parmaName 参数
     * @return
     */
    @Override
    public String getSelectBySpecifyFirstSql(EntityTable table, String parmaName) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT TOP 1 ");
        sql.append(getSelectBySpecifyBaseSql(parmaName));
        sql.append(" FROM ");
        sql.append(MybatisSqlUtils.getTableOrViewName(table));
        sql.append(lockMode(String.format("%s.lockMode", parmaName)));
        sql.append(SqlServerUtils.createSectionWhereSql(parmaName));
        sql.append(getOrder(table));
        return sql.toString();
    }

    /**
     * 分页查询sql
     *
     * @param table
     * @param columns
     * @param parmaName
     * @return
     */
    private String selectPageSql(EntityTable table, String columns, String parmaName) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        sql.append(columns);
        sql.append(" FROM (");
        sql.append("SELECT  ROW_NUMBER() OVER (");
        sql.append(getOrder(table));
        sql.append(" )  as rownum , ");
        sql.append(MybatisSqlUtils.getAllColumns(table));
        sql.append(" FROM ");
        sql.append(MybatisSqlUtils.getTableOrViewName(table));
        sql.append(lockMode(String.format("%s.lockMode", parmaName)));
        sql.append(SqlServerUtils.createSectionWhereSql(parmaName));
        sql.append(") AS P ");
        return sql.toString();
    }

    /**
     * 只有条件查询的sql
     *
     * @param table
     * @param columns
     * @param parmaName
     * @return
     */
    private String selectSql(EntityTable table, String columns, String parmaName) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        sql.append(columns);
        sql.append(" FROM ");
        sql.append(MybatisSqlUtils.getTableOrViewName(table));
        sql.append(lockMode(String.format("%s.lockMode", parmaName)));
        sql.append(SqlServerUtils.createSectionWhereSql(parmaName));
        sql.append(getOrder(table));
        return sql.toString();
    }

    /**
     * top 查询sql
     *
     * @param table
     * @param columns
     * @param parmaName
     * @return
     */
    private String selectTopSql(EntityTable table, String columns, String parmaName) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT TOP ");
        sql.append("${section.pageClause.limit} ");
        sql.append(columns);
        sql.append(" FROM ");
        sql.append(MybatisSqlUtils.getTableOrViewName(table));
        sql.append(lockMode(String.format("%s.lockMode", parmaName)));
        sql.append(SqlServerUtils.createSectionWhereSql(parmaName));
        sql.append(getOrder(table));
        return sql.toString();
    }

}
